/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controller;

import Model.login;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.http.HttpSession;

/**
 *
 * @author Martin Spencer Sy
 */
public class ProductManager {
    private static Connection con;
    private static Statement s;
    private static String sql;
    private static ResultSet rs;
    
    public static ResultSet getAllProducts() throws ClassNotFoundException, SQLException {
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "SELECT * FROM listProduct ORDER BY name ASC";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getProduct(String product) throws ClassNotFoundException, SQLException {
        
        PreparedStatement ps = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            sql = "SELECT * FROM listProduct WHERE pindex=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, product);
            rs = ps.executeQuery();
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
    }
    
    public static ResultSet getProductForManager(String product, String productType) throws ClassNotFoundException, SQLException {
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "SELECT * FROM listProductManager WHERE pindex='" + product + "' AND pType=" + productType + "";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return rs;
        
    }
    
    public static ResultSet getProductComments(String product) throws ClassNotFoundException, SQLException {
        
        PreparedStatement ps = null;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            sql = "SELECT * FROM listUserComment WHERE pindex=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, product);
            rs = ps.executeQuery();
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver classs not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        return rs;
    }
    
    public static ResultSet getProductTypes(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "SELECT type FROM productType";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        return rs;
    }
    
    public static ResultSet getProductGenres(){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");

            s = con.createStatement();
            sql = "SELECT genre FROM productGenre";
            rs = s.executeQuery(sql);
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        return rs;
    }
    
    public static boolean commentProduct(String product, String comment, HttpSession session){
        PreparedStatement ps = null;
        int updateResult,
            user,
            productID;
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            user = Integer.parseInt( (String)session.getAttribute("user") );
            
            sql = "SELECT pid FROM listUserBought WHERE pindex=? AND uid=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, product);
            ps.setInt(2, user);
            rs = ps.executeQuery();
            
            if( rs.next() ){//if the user has bought the product
                
                sql = "CALL addComment(?, ?, ?)";
                ps = con.prepareStatement(sql);
                ps.setInt(1, user);
                ps.setString(2, product);
                ps.setString(3, comment);
                updateResult = ps.executeUpdate();

                //if the insert command is successful go back
                //to the viewProduct page with no errors
                if(updateResult == 1){
                    closeDB();
                    return true;
                }
                else{
                    closeDB();
                    return false;
                }
                
            }
            else{
                return false;
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        
        return false;
    }
    
    public static int addProduct(String name, String synopsis, String genre, String type, String price){
        PreparedStatement ps = null;
        int insertResult;
        float convertedPrice = Float.parseFloat( (String)price );
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            //check if the name and type of the product already exists in the db
            sql = "SELECT pid FROM listProduct WHERE name=? AND type=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, type);
            rs = ps.executeQuery();
            
            if ( !(rs.next()) ) {//if name and type of the product is not yet in db add product
                sql = "CALL addProduct(?, ?, ?, ?, ?, ?)";
            
                ps = con.prepareStatement(sql);

                ps.setString(1, name);
                ps.setString(2, synopsis);
                ps.setString(3, genre);
                ps.setString(4, type);
                ps.setFloat(5, convertedPrice);
                ps.setString( 6, name.replace(" ", "_").replace("'", "") );

                insertResult = ps.executeUpdate();

                if(insertResult == 1){//if the product is successfully added to the db
                    closeDB();
                    return 1;
                }
                else{
                    closeDB();
                    return -1;
                }
            } else {
                return 0;
            }
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        return -1;
    }
    
    public static int editProduct(String name, String synopsis,
            String genre, String type, String price, String status, String index){
        
        PreparedStatement ps = null;
        int updateResult,
                convertedStatus = 0;
        float convertedPrice = Float.parseFloat( price );
        
        if(status != null){
            convertedStatus = Integer.parseInt( status );
        }
        
        try{
            Class.forName("com.mysql.jdbc.Driver");
            
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/foobar", 
                    "root", "");
            
            //check if the name and type of the product already exists in the db
            sql = "SELECT pid FROM listProduct WHERE name=? AND type=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, name);
            ps.setString(2, type);
            rs = ps.executeQuery();
            
            if( !(rs.next()) ){//if the name and type does not yet exist in the db
                System.out.println( "This is replaced: " + name.replace(" ", "_").replace("'", "") );
                //query to edit product
                sql = "CALL editProduct(?, ?, ?, ?, ?, ?, ?, ?)";
                ps = con.prepareStatement(sql);
                ps.setString(1, index);
                ps.setString(2, name);
                ps.setString(3, synopsis);
                ps.setString(4, genre);
                ps.setString(5, type);
                ps.setFloat(6, convertedPrice);
                ps.setInt(7, convertedStatus);
                ps.setString( 8, name.replace(" ", "_").replace("'", "") );
                updateResult = ps.executeUpdate();

                if( updateResult == 1 ){//if the update was successfull
                    closeDB();
                    return 1;
                }
                else{
                    closeDB();
                    return -1;
                }
            }
            else{
                //if the product name was not changed then it must check if the product is unique in terms of the index
                sql = "SELECT pid FROM listProduct WHERE pindex=?";
                ps = con.prepareStatement(sql);
                ps.setString(1, index);
                rs = ps.executeQuery();
                
                if( rs.next() ){//if the product is still unique than edit product
                    
                    //sql query to edit product
                    sql = "CALL editProduct(?, ?, ?, ?, ?, ?, ?, ?)";
                    ps = con.prepareStatement(sql);
                    ps.setString(1, index);
                    ps.setString(2, name);
                    ps.setString(3, synopsis);
                    ps.setString(4, genre);
                    ps.setString(5, type);
                    ps.setFloat(6, convertedPrice);
                    ps.setInt(7, convertedStatus);
                    ps.setString( 8, name.replace(" ", "_").replace("'", "") );
                    updateResult = ps.executeUpdate();

                    if( updateResult == 1 ){//if update was successful
                        closeDB();
                        return 1;
                    }
                    else{
                        closeDB();
                        return -1;
                    }
                }
                else{//if tghhe product already exists
                    closeDB();
                    return 0;
                }
            }
            
        }
        catch (SQLException ex) {
            //Logger.getLogger(login.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
        catch (ClassNotFoundException e1) {
            // JDBC driver class not found, print error message to the console
            System.out.println(e1.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Class Not Found Exception: " + e1.getMessage(), null, null);
        }
        catch (Exception e3) {
            // other unexpected exception, print error message to the console
            System.out.println(e3.toString());
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught Exception: " + e3.getMessage(), null, null);
        }
        return -1;
    }
    
    //method for closing the database connection
    public static void closeDB(){
        try {
            con.close();
            rs.close();
            s.close();
        } 
        catch (SQLException ex) {
            //Logger.getLogger(ProductManager.class.getName()).log(Level.SEVERE, null, ex);
            LogManager.logEvent(ProductManager.class.getName() + "." + Thread.currentThread().getStackTrace()[1].getMethodName(), Level.SEVERE, "Caught SQL Exception: " + ex.getMessage(), null, null);
        }
    }
}
